package com.cwt

import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.SparkSession

object SparkSQL {
  def main(args: Array[String]): Unit = {
    val sparkConf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("sparkDSL")
    val spark: SparkSession = SparkSession.builder().config(sparkConf).getOrCreate()
    val sc: SparkContext = spark.sparkContext
    sc.setLogLevel("WARN")
    //加载数据
//    1. 读取文件的数据test.txt
    val rdd1 = sc.textFile(this.getClass.getClassLoader.getResource("test.txt").getPath).map(x => x.split(" "))
    val personRDD = rdd1.map(x => Person(x(0), x(1), x(2).toInt, x(3), x(4), x(5).toInt))
    import spark.implicits._

    val personDF = personRDD.toDF
    personDF.printSchema
    personDF.createTempView("person")

//    2. 一共有多少个小于20岁的人参加考试
    println("2. 一共有多少个小于20岁的人参加考试")
    spark.sql("select count(*) from person where age<20").show

//    3. 一共有多少个等于20岁的人参加考试
    println("3. 一共有多少个等于20岁的人参加考试")
    spark.sql("select count(*) from person where age=20").show

//    4. 一共有多少个大于20岁的人参加考试
    println("4. 一共有多少个大于20岁的人参加考试")
    spark.sql("select count(*) from person where age>20").show

//    5. 一共有多个男生参加考试？
    println("5. 一共有多个男生参加考试？")
    spark.sql("select count(*) from person where sex='男'").show

//    6. 一共有多少个女生参加考试？
    println("6. 一共有多少个女生参加考试？")
    spark.sql("select count(*) from person where sex='女'").show

//    7. 12班有多少人参加考试？
    println("7. 12班有多少人参加考试？")
    spark.sql("select count(*) from person where classId='12'").show

//    8. 13班有多少人参加考试？
    println("8. 13班有多少人参加考试？")
    spark.sql("select count(*) from person where classId='13'").show

//    9. 语文科目的平均成绩是多少？
    println("9. 语文科目的平均成绩是多少？")
    spark.sql("select avg(score) from person where course='chinese'").show

//    10. 数学科目的平均成绩是多少？
    println("10. 数学科目的平均成绩是多少？")
    spark.sql("select avg(score) from person where course='math'").show

//    11. 英语科目的平均成绩是多少？
    println("11. 英语科目的平均成绩是多少？")
    spark.sql("select avg(score) from person where course='english'").show

//    12. 每个人平均成绩是多少？
    println("12. 每个人平均成绩是多少？")
    spark.sql("select name, avg(score) from person where group by name").show

//    13. 12班平均成绩是多少？
    println("13. 12班平均成绩是多少？")
    spark.sql("select avg(score) from person where classId='12'").show

//    14. 12班男生平均总成绩是多少？
    println("14. 12班男生平均总成绩是多少？")
    spark.sql("select avg(score) from person where classId='12' and sex='男'").show

//    15. 12班女生平均总成绩是多少？
    println("15. 12班女生平均总成绩是多少？")
    spark.sql("select avg(score) from person where classId='12' and sex='女'").show

//    16. 13班平均成绩是多少？
    println("16. 13班平均成绩是多少？")
    spark.sql("select avg(score) from person where classId='13'").show

//    17. 13班男生平均总成绩是多少？
    println("17. 13班男生平均总成绩是多少？")
    spark.sql("select avg(score) from person where classId='13' and sex='男'").show

//    18. 13班女生平均总成绩是多少？
    println("18. 13班女生平均总成绩是多少？")
    spark.sql("select avg(score) from person where classId='13' and sex='女'").show

//    19. 全校语文成绩最高分是多少？
    println("19. 全校语文成绩最高分是多少？")
    spark.sql("select max(score) from person where course='chinese'").show

//    20. 12班语文成绩最低分是多少？
    println("20. 12班语文成绩最低分是多少？")
    spark.sql("select min(score) from person where course='chinese' and classId='12'").show

//    21. 13班数学最高成绩是多少？
    println("21. 13班数学最高成绩是多少？")
    spark.sql("select max(score) from person where course='math' and classId='13'").show

//    22. 总成绩大于150分的12班的女生有几个？
    println("22. 总成绩大于150分的12班的女生有几个？")
    spark.sql("select count(*) from person where sex='女' and classId='12' group by name having sum(score)>150").show

//    23. 总成绩大于150分，且数学大于等于70，且年龄大于等于19岁的学生的平均成绩是多少？
    println("23. 总成绩大于150分，且数学大于等于70，且年龄大于等于19岁的学生的平均成绩是多少？")
    spark.sql("select name, avg(score) from person where name in (select name from person where age>=19 and course='math' and score>=70) group by name having sum(score)>150").show

    spark.stop()
  }
} 